/**************************************************************

This do file imputes annual population counts from the 1990, 1995, 2000, 2010, and 2015 Census;
**************************************************************/
capture log close
capture program drop _all
capture macro drop _all
drop _all
set more off

global raw_data "/Users/ctheoharides/Box/Philippine Nurse Migration Research/Population Data/Raw Data/"
global here "/Users/ctheoharides/Box/Philippine Nurse Migration Research/Population Data/Cleaned Data/"

***************************************************************
insheet using "${raw_data}population from 1990-2015.csv", names

gen rate2010=(y2010/y2000)^(1/10)
gen rate2015=(y2015/y2010)^(1/5)
gen rate1995=(y1995/y2000)^(1/5)
gen rate1990=(y1990/y1995)^(1/5)

drop y*
save "${here}growrates.dta", replace

***CALCULATING TOTAL POPULATION BY PROVINCE-YEAR AND EXTRAPOLATING FUTURE VALUES*****

use "${raw_data}census2000.dta", clear

keep if age>=18 & age<=60 
collapse (sum) wtper, by(geo2b_ph sex)
reshape wide wtper, i(geo2b_ph) j(sex)
rename wtper1 totpop_m2000
rename wtper2 totpop_f2000
gen totpop2000=totpop_m2000+totpop_f2000

decode geo2b_ph, gen(province)													//clean province codes for later matching
replace province=upper(province)
replace province="FIRST DISTRICT" if province=="MANILA, METRO FIRST DISTRICT"
replace province="SECOND DISTRICT" if province=="METRO MANILA, SECOND DISTRICT"
replace province="THIRD DISTRICT" if province=="METRO MANILA, THIRD DISTRICT"
replace province="FOURTH DISTRICT" if province=="METRO MANILA, FOURTH DISTRICT"
replace province="COTABATO" if province=="COTABATO (NORTH COTABATO)"
replace province="DAVAO DEL NORTE" if province=="DAVAO (DAVAO DEL NORTE)"
replace province="SAMAR" if province=="SAMAR (WESTERN SAMAR)"

merge 1:1 province using "${here}growrates.dta"
keep if _m==3
drop _m geo2b_ph

forvalues i=2001(1)2010 {
	local j=`i'-1
	gen totpop`i'=totpop`j'*rate2010
	gen totpop_m`i'=totpop_m`j'*rate2010
	gen totpop_f`i'=totpop_f`j'*rate2010
	}

forvalues i=2011(1)2015 {
	local j=`i'-1
	gen totpop`i'=totpop`j'*rate2015
	gen totpop_m`i'=totpop_m`j'*rate2015
	gen totpop_f`i'=totpop_f`j'*rate2015
	}

gen totpop1999=totpop2000*rate1995
gen totpop1998=totpop1999*rate1995
gen totpop1997=totpop1998*rate1995
gen totpop1996=totpop1997*rate1995
gen totpop1995=totpop1996*rate1995

gen totpop1994=totpop1995*rate1990
gen totpop1993=totpop1994*rate1990
gen totpop1992=totpop1993*rate1990
gen totpop1991=totpop1992*rate1990
gen totpop1990=totpop1991*rate1990

gen totpop1989=totpop1990*rate1990
gen totpop1988=totpop1989*rate1990

gen totpop_m1999=totpop_m2000*rate1995
gen totpop_m1998=totpop_m1999*rate1995
gen totpop_m1997=totpop_m1998*rate1995
gen totpop_m1996=totpop_m1997*rate1995
gen totpop_m1995=totpop_m1996*rate1995

gen totpop_m1994=totpop_m1995*rate1990
gen totpop_m1993=totpop_m1994*rate1990
gen totpop_m1992=totpop_m1993*rate1990
gen totpop_m1991=totpop_m1992*rate1990
gen totpop_m1990=totpop_m1991*rate1990

gen totpop_m1989=totpop_m1990*rate1990
gen totpop_m1988=totpop_m1989*rate1990

gen totpop_f1999=totpop_f2000*rate1995
gen totpop_f1998=totpop_f1999*rate1995
gen totpop_f1997=totpop_f1998*rate1995
gen totpop_f1996=totpop_f1997*rate1995
gen totpop_f1995=totpop_f1996*rate1995

gen totpop_f1994=totpop_f1995*rate1990
gen totpop_f1993=totpop_f1994*rate1990
gen totpop_f1992=totpop_f1993*rate1990
gen totpop_f1991=totpop_f1992*rate1990
gen totpop_f1990=totpop_f1991*rate1990

gen totpop_f1989=totpop_f1990*rate1990
gen totpop_f1988=totpop_f1989*rate1990

foreach var of var totpop* totpop_m* totpop_f*{
	replace `var'=round(`var',1)
	}

drop rate*
order province province totpop1990 totpop1991 totpop1992 						///
totpop1993 totpop1994 totpop1995 totpop1996 totpop1997 							///
totpop1998 totpop1999 totpop2000

reshape long totpop totpop_m totpop_f, i(province) j(year)

save "${here}totpop.dta", replace


***CALCULATING COLLEGE POPULATION BY PROVINCE AND EXTRAPOLATING FUTURE VALUES***

clear
use "${raw_data}census2000.dta", clear

keep if age>=18 & age<=22
collapse (sum) wtper, by(geo2b_ph sex)
reshape wide wtper, i(geo2b_ph) j(sex)
rename wtper1 collegepop_m2000
rename wtper2 collegepop_f2000
gen collegepop2000=collegepop_m+collegepop_f2000

decode geo2b_ph, gen(province)													//facilitating matching
replace province=upper(province)
replace province="FIRST DISTRICT" if province=="MANILA, METRO FIRST DISTRICT"
replace province="SECOND DISTRICT" if province=="METRO MANILA, SECOND DISTRICT"
replace province="THIRD DISTRICT" if province=="METRO MANILA, THIRD DISTRICT"
replace province="FOURTH DISTRICT" if province=="METRO MANILA, FOURTH DISTRICT"
replace province="COTABATO" if province=="COTABATO (NORTH COTABATO)"
replace province="DAVAO DEL NORTE" if province=="DAVAO (DAVAO DEL NORTE)"
replace province="SAMAR" if province=="SAMAR (WESTERN SAMAR)"

merge 1:1 province using "${here}growrates.dta"
keep if _m==3
drop _m geo2b_ph

forvalues i=2001(1)2010 {
	local j=`i'-1
	gen collegepop`i'=collegepop`j'*rate2010	
	gen collegepop_m`i'=collegepop_m`j'*rate2010
	gen collegepop_f`i'=collegepop_f`j'*rate2010
	}

forvalues i=2011(1)2015 {
	local j=`i'-1
	gen collegepop`i'=collegepop`j'*rate2015
	gen collegepop_m`i'=collegepop_m`j'*rate2015
	gen collegepop_f`i'=collegepop_f`j'*rate2015
	}

gen collegepop1999=collegepop2000*rate1995
gen collegepop1998=collegepop1999*rate1995
gen collegepop1997=collegepop1998*rate1995
gen collegepop1996=collegepop1997*rate1995
gen collegepop1995=collegepop1996*rate1995

gen collegepop1994=collegepop1995*rate1990
gen collegepop1993=collegepop1994*rate1990
gen collegepop1992=collegepop1993*rate1990
gen collegepop1991=collegepop1992*rate1990
gen collegepop1990=collegepop1991*rate1990

gen collegepop1989=collegepop1990*rate1990
gen collegepop1988=collegepop1989*rate1990

gen collegepop_m1999=collegepop_m2000*rate1995
gen collegepop_m1998=collegepop_m1999*rate1995
gen collegepop_m1997=collegepop_m1998*rate1995
gen collegepop_m1996=collegepop_m1997*rate1995
gen collegepop_m1995=collegepop_m1996*rate1995

gen collegepop_m1994=collegepop_m1995*rate1990
gen collegepop_m1993=collegepop_m1994*rate1990
gen collegepop_m1992=collegepop_m1993*rate1990
gen collegepop_m1991=collegepop_m1992*rate1990
gen collegepop_m1990=collegepop_m1991*rate1990

gen collegepop_m1989=collegepop_m1990*rate1990
gen collegepop_m1988=collegepop_m1989*rate1990

gen collegepop_f1999=collegepop_f2000*rate1995
gen collegepop_f1998=collegepop_f1999*rate1995
gen collegepop_f1997=collegepop_f1998*rate1995
gen collegepop_f1996=collegepop_f1997*rate1995
gen collegepop_f1995=collegepop_f1996*rate1995

gen collegepop_f1994=collegepop_f1995*rate1990
gen collegepop_f1993=collegepop_f1994*rate1990
gen collegepop_f1992=collegepop_f1993*rate1990
gen collegepop_f1991=collegepop_f1992*rate1990
gen collegepop_f1990=collegepop_f1991*rate1990

gen collegepop_f1989=collegepop_f1990*rate1990
gen collegepop_f1988=collegepop_f1989*rate1990

foreach var of var collegepop* collegepop_m* collegepop_f* {
	replace `var'=round(`var',1)
	}

drop rate*
order province province collegepop1990 collegepop1991 collegepop1992 			
collegepop1993 collegepop1994 collegepop1995 collegepop1996 collegepop1997 		
collegepop1998 collegepop1999 collegepop2000

reshape long collegepop collegepop_m collegepop_f, i(province) j(year)

save "${here}collegepop.dta", replace 
